import MySQLdb
from zongheng.items import ZonghengItem, ChapterItem


class ZonghengPipeline:

    def __init__(self):
        self.__db_config = {
            'host': 'localhost',
            'user': 'root',  # 替换为你的用户名
            'password': '',  # 替换为你的密码
            'database': 'bilibili',  # 替换为你的数据库名
            'port': 3307,
            'autocommit': True  # 自动提交事务
        }
        # 建立数据库连接
        self.conn = MySQLdb.connect(**self.__db_config)
        self.cursor = self.conn.cursor()
        # 缓存书的基本信息
        self.cache_data_list = []
        # 缓存书的内容
        self.chapter_list = []

    def process_item(self, item, spider):
        """保存小说的基本信息"""
        if isinstance(item, ZonghengItem):
            self.cache_data_list.append(
                (int(item['book_id']), item['book_name'], item['author'], item['book_type'], item['story_link']))
            if len(self.cache_data_list) >= 50:
                sql1 = """INSERT INTO tb_zongheng (id, book_name, author, book_type, story_link)
                         VALUES (%s, %s, %s, %s, %s)
                                  """
                self.cursor.executemany(sql1, self.cache_data_list)
                self.conn.commit()
                print("插入成功")
                self.cache_data_list.clear()
        if isinstance(item, ChapterItem):
            self.chapter_list.append(
                (int(item['chapter_id']), item['chapter_name'], item['content'], int(item['book_id']))
            )
            if len(self.chapter_list) >= 3000:
                sql2 = """
                insert into tb_story(chapter_id, chapter_name, content, book_id) values (%s, %s, %s, %s)
                """
                self.cursor.executemany(sql2, self.chapter_list)
                self.conn.commit()
                print("插入成功")
                self.chapter_list.clear()

        return item

    def close_spider(self, spider):
        if len(self.cache_data_list) > 0:
            sql = """INSERT INTO tb_zongheng (id, book_name, author, book_type, story_link)
                     VALUES (%s, %s, %s, %s, %s)
                              """
            self.cursor.executemany(sql, self.cache_data_list)
            self.conn.commit()

        if len(self.chapter_list) > 0:
            sql2 = """
                        insert into tb_story(chapter_id, chapter_name, content, book_id) values (%s, %s, %s, %s)
                        """
            self.cursor.executemany(sql2, self.chapter_list)
            self.conn.commit()
        # 关闭游标
        self.conn.close()
        # 关闭连接
        self.cursor.close()
